#!/usr/bin/python
# -*- coding: UTF-8 -*-

"""
@company:ZYF
@author:yu.qian
@file:pd_excel.py
@time:2022/10/27
"""
import io
import xlrd
import pandas as pd
from xlsxwriter.workbook import Workbook


# def demo_upload(filename, data):
#     config = {"key_id": "AKIDwf8uQfcIgndCTTBcmfHfma7NLLIzPFtH",
#               "secret": "B5h38vQdZFCsHTP4EDwPdbKnzuZx5wXr",
#               "bucket": "szhss-1313454947",  # 桶名
#               "region": "ap-nanjing",
#               "host": "cos.ap-nanjing.myqcloud.com"
#               }
#     from storage import CosOss
#
#     oss = CosOss(**config)
#
#     oss.upload(filename, data)
#
#     print(oss.download_url(filename))


# excel的表头
struct_columns = ["日期", "网络购票人数", "线下购票人数", "团队购票人数", "总购票人数"]
# 数据拼接的结构必须为[[]] ，内部列表的长度必须跟表头长度一致
datas = [
    ["2022/10/27", 42, 35, 23, 160]
]


def gen_rspd_data_by_pandas():
    """
    pandas写入excel
    """
    df_detail = pd.DataFrame(datas, columns=struct_columns)
    bio = io.BytesIO()

    with pd.ExcelWriter(bio, engine='xlsxwriter', engine_kwargs={'options': {'strings_to_urls': False}}) as writer:
        # 答卷数据
        df_detail.to_excel(writer, sheet_name='data', index=False)
        df_detail_shape = df_detail.shape[1]
        workbook = writer.book
        format_border = workbook.add_format({'border_color': '#e1e4eb'})  # 设置边框格式
        worksheets = writer.sheets
        worksheet1 = worksheets['data']
        # 这里是核心，根据条件来设置格式
        worksheet1.conditional_format(0, 0, 0, df_detail_shape, {'type': 'no_blanks', 'format': format_border})
        writer.save()
        bio.seek(0)
        data = bio.read()
    return data


def gen_rspd_data_by_xlsxwriter():
    """
    xlsxwriter写入excel
    """
    # excel表头
    q_headers = ["日期", "网络购票人数", "线下购票人数", "团队购票人数", "总购票人数"]
    # filename = f'日常购票.xlsx'
    stream = io.BytesIO()
    wbk = Workbook(stream)
    # 添加固定的一个sheet页
    ticket_sheet = wbk.add_worksheet("票务人数统计")
    # A1行写入表头
    ticket_sheet.write_row('A1', q_headers)

    # 与pandas一致，需要内部列表长度跟表头保持一致
    row_datas = [["2022/10/27", 42, 35, 23, 160]]
    for index, row in enumerate(row_datas, 2):
        # 依次把数据写入具体index行
        ticket_sheet.write_row("A%s" % index, row)
    wbk.close()
    stream.seek(0)
    return stream.read()


def read_excel_data(excel_file_content: bytes):
    """
    # tornado
    file_metas = self.request.files.get('file')
    meta = file_metas[0]
    file_data = meta['body']
    filename = meta['filename']

    # odoo
    file_metas = vals.get('filename')
    filename = file_metas.filename
    file_data = file_metas.stream.read()
    或者
    files = request.httprequest.files.get('filename')

    # flask
    读取二进制流数据并解码
    @app.route("/file", methods=['GET', 'POST'])
    def getFile():
        fileStorage = request.files['file']
        content = fileStorage.read().decode('ascii')

    # django
    def upload_device(request):
        f = request.FILES.get('file')
        name = f.name
        content = f.read()
    """

    book = xlrd.open_workbook(file_contents=excel_file_content)
    data_sheet = book.sheet_by_index(0)  # data sheet
    data_list = []
    if len(book.sheets()) > 0:
        for rownum in range(1, data_sheet.nrows):
            data_list.append([str(col).replace(".0", "").strip() for col in data_sheet.row_values(rownum)])
    return data_list


# if __name__ == '__main__':
#     # gen_rspd_data_by_pandas()
#     filename = 'demo1.xlsx'
#     data = gen_rspd_data_by_xlsxwriter()
#     demo_upload(filename, data)
